"""
Copyright (c) 2023 - present SKbioinfo
"""

from apps.NGS_API.connect import MysqlConnection
from apps.NGS_API.common.db_table_config import *

def get_ngsLib_info():
    ### set up MYSQL connection ###
    db_conn=MysqlConnection()
    ### SQL: bioana_base_info ###
    ngsLib_base_info_sql='''
        SELECT
            {table_alias_one}.id, 
            {table_alias_three}.project_id,
            {table_alias_one}.library_id,
            {table_alias_one}.stock_id,
            {table_alias_one}.sample_id,
            {table_alias_two}.sequencing_id,
            {table_alias_two}.seq_runid,
            {table_alias_two}.seq_lane,
            {table_alias_two}.barcode1,
            {table_alias_two}.barcode2,
            {table_alias_one}.create_time,
            {table_alias_one}.operate_user,
            {table_alias_one}.check_user,
            {table_alias_one}.modify_time
        FROM {join_table_one} as {table_alias_one}
        LEFT JOIN {join_table_two} as {table_alias_two} ON
        {table_alias_one}.{join_col} = {table_alias_two}.{join_col}
        LEFT JOIN {join_table_three} as {table_alias_three} ON
        {table_alias_one}.{join_col} = {table_alias_three}.{join_col}
    '''.format(
        join_table_one=ngsLib_db["name"],
        join_table_two=sequencing_db["name"],
        join_table_three=sample_db["name"],
        table_alias_one="n_lib",
        table_alias_two="n_seq",
        table_alias_three="n_sam",
        join_col="sample_id"
        
    )
    ngsLib_base_info = db_conn.Query(ngsLib_base_info_sql)
    db_conn.close_conn()
    return ngsLib_base_info